 /**
 * TDS PEC 4
 * Grupo 3H2M
 *
 * El paquete <code>edu.uoc.tds.pec4.server.bbdd</code> agrupa todas las clases
 * que encapsulan los objetos de acceso a datos (DAO),para realizar el acceso
 * a datos. La base de datos es PostgreSQL.
 */
package edu.uoc.tds.pec4.server.bbdd;

import edu.uoc.tds.pec4.beans.CiudadDTO;
import edu.uoc.tds.pec4.beans.PaisDTO;
import edu.uoc.tds.pec4.beans.ProvinciaDTO;
import edu.uoc.tds.pec4.beans.RentabilidadAulaDTO;
import edu.uoc.tds.pec4.beans.RentabilidadClienteDTO;
import edu.uoc.tds.pec4.beans.RentabilidadFacturacionDTO;
import edu.uoc.tds.pec4.beans.RentabilidadFacturacionMediaDTO;
import edu.uoc.tds.pec4.beans.RentabilidadLocalizacionDTO;
import edu.uoc.tds.pec4.beans.RentabilidadReservasDTO;
import edu.uoc.tds.pec4.beans.RentabilidadSolicitudesDTO;
import edu.uoc.tds.pec4.common.filters.FiltroFechas;
import edu.uoc.tds.pec4.common.filters.FiltroLocalizacion;
import edu.uoc.tds.pec4.common.util.Constantes;
import edu.uoc.tds.pec4.server.bbdd.exceptions.DataBaseDAOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * La clase <code>IncidenciaDAO</code> es el componente de software que suministra
 * una interfaz común entre la aplicación y la base de datos, relacionada con la
 * gestión de las incidencias y sus anotaciones.<br />
 * Extiende la clase <code>DataBaseDAO</code><br />
 * 
 * @author	Isabel Guerra Monclova
 * @version	05/12/2011
 * @see         edu.uoc.tds.pec4.server.bbdd.exceptions.DataBaseDAOException
 */
public class RentabilidadDAO extends DataBaseDAO implements IRentabilidadDAO {

    public RentabilidadDAO () throws DataBaseDAOException {
        super();
    }
    
    @Override
    public List<RentabilidadAulaDTO> getRentabilidadAulas(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int tipoLocalizacion, int top) throws DataBaseDAOException{
        
      List<RentabilidadAulaDTO> lista = new ArrayList<RentabilidadAulaDTO>();
      try {       
        //Siempre tienen que llegar fechas, si el usuario no indica ninguna, presentación
        //deberá añadir unas por defecto, ejemplo inicio de la aplicación hasta el día actual
        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String fechaDesde = formato.format(filtroFechas.getFechaInicio());
        String fechaHasta = formato.format(filtroFechas.getFechaFin());

        // Construimos la lista de localizaciones para obtener solo esas aulas
        String condicionLocalizacion="";
        String tablasLocalizacion="";
        if (tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PAISES) {
            String paises = obtenerListaPaises(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idpais in ("+paises+")";
            tablasLocalizacion = "ciudad c, provincia p ";
        } else if (tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PROVINCIAS) {
            String provincias = obtenerListaProvincias(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idprovincia in ("+provincias+")";
            tablasLocalizacion = "ciudad c ";
        } else {
            String ciudades = obtenerListaCiudades(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idciudad in ("+ciudades+")";
            tablasLocalizacion = "ciudad c ";
        }
        
        //Obtenemos los datos de las medias globales para calcular la posición del aula respecto a ella
        //Los valores globales será la media de asistentes/número de reservas totales y la facturación total/número de reservas
        //Las reservas pueden estar consumadas o canceladas, ya que si estuvieran pendientes aún no se sabría el importe que
        //se va a recibir de ellas
        String sSQLGlobal = "select sum(importe) as importeTotal, sum(asistentes) as asistentes, count(idreserva) as reservas "
                + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and  "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and "
                + "(r.estado="+Constantes.RESERVA_CONSUMADA+" or r.estado="+Constantes.RESERVA_CANCELADA+") and "
                + condicionLocalizacion;
        
        ResultSet objRSGlobal = super.obtenerConsulta(sSQLGlobal);
        Float mediaGlobalAsistentes = new Float(0.00);
        Float mediaGlobalFacturacion = new Float(0.00);
        if(objRSGlobal.next()) {
            mediaGlobalAsistentes = (objRSGlobal.getFloat("asistentes")) / (objRSGlobal.getFloat("reservas"));
            mediaGlobalFacturacion = (objRSGlobal.getFloat("importeTotal")) / (objRSGlobal.getFloat("reservas"));
        }
        // Liberamos recursos
        objRSGlobal.close();
        objRSGlobal = null;
             
        //Datos globales de un aula - solo cuenta consumadas o canceladas, reservas pendientes no se han cobrado todavía
        String sSQL = "select a.idaula as idAula, a.nombre as nomAula, count(s.idcliente) as clientes, count(r.idreserva) as reservas, "
                + "sum(importe) as importeTotal, sum(asistentes) as asistentes "
                + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                + "(s.fechaini>='"+fechaDesde+"' and s.fechafin<='"+fechaHasta+"') and "
                + "(r.estado="+Constantes.RESERVA_CONSUMADA+" or r.estado="+Constantes.RESERVA_CANCELADA+") and "
                + condicionLocalizacion                
                + "group by a.idaula,a.nombre";
        
        ResultSet objRS = super.obtenerConsulta(sSQL);
        String idAula;
        Integer asistentes, reservas;
        Float importe = new Float(0.00);
        boolean continuar = true; //variable que controla el número de filas a obtener según el valor de Top
        int numFilas = 0;
        while (objRS.next()&&continuar) { //por cada aula, casos particulares reservas consumadas vs reservas canceladas

            RentabilidadAulaDTO rentabilidad = new RentabilidadAulaDTO();
            idAula = objRS.getString("idAula");
            rentabilidad.setAula(objRS.getString("nomAula"));  
            
            RentabilidadReservasDTO res = new RentabilidadReservasDTO();
            reservas = objRS.getInt("reservas");
            res.setTotalReservas(reservas);
            
            asistentes = objRS.getInt("asistentes");
            rentabilidad.setAsistentes(asistentes);
            rentabilidad.setClientes(objRS.getInt("clientes"));
            
            RentabilidadFacturacionDTO fac = new RentabilidadFacturacionDTO();
            RentabilidadFacturacionMediaDTO facMedia = new RentabilidadFacturacionMediaDTO();
            importe = objRS.getFloat("importeTotal");
            fac.setTotal(importe);
            
            //solo puede haber un resultado o el valor será 0
            Float mediaAsistentes = new Float(0.00);
            Float mediaFacturacion = new Float(0.00);
            if((reservas!=0)&&(asistentes!=0)) 
                    mediaAsistentes = new Float(asistentes / reservas);
            if((reservas!=0)&&(importe!=0))
                mediaFacturacion = new Float(importe / reservas);
            
            rentabilidad.setMediaAsistentes(mediaAsistentes);
            facMedia.setMedia(mediaFacturacion);
            
            Float diferencia = new Float(0.00);
            //Calculamos las diferencias con respecto a los datos globales, media de asistentes y media de facturación
            if(mediaGlobalFacturacion==0) {//si la global fuera 0 porque aún no hay reservas consumadas o canceladas la diferencia también será 0.00
                    facMedia.setDiferencia(diferencia);
            }else {
                    if(mediaFacturacion>=mediaGlobalFacturacion) { //diferencia positiva
                        diferencia = (mediaFacturacion * 100) / mediaGlobalFacturacion; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaFacturacion * 100) / mediaGlobalFacturacion);
                    }
                    facMedia.setDiferencia(diferencia);
            }
            if(mediaGlobalAsistentes==0) {//si la global fuera 0 porque aún no hay reservas consumadas o canceladas la diferencia también será 0.00
                    rentabilidad.setDiferenciaMediaAsistentes(mediaGlobalAsistentes);
            }else {
                    diferencia = new Float(0.00);
                    if(mediaAsistentes>=mediaGlobalAsistentes) { //diferencia positiva
                        diferencia = (mediaAsistentes * 100) / mediaGlobalAsistentes; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaAsistentes * 100) / mediaGlobalAsistentes);
                    }
                    rentabilidad.setDiferenciaMediaAsistentes(diferencia);
            }           
            // Obtenemos los datos de reservas consumadas
            String sSQL2 = "select count(r.idreserva) as reservas, sum(r.importe) as importeTotal "
                    + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                    + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                    + "a.idaula="+idAula+" and r.estado="+Constantes.RESERVA_CONSUMADA+" and "
                    + "a.idciudad=c.idciudad and "
                    + condicionLocalizacion;
            
            ResultSet objRAceptadas = super.obtenerConsulta(sSQL2);
            //solo puede haber un resultado o el valor será 0
            if (objRAceptadas.next()) {
                res.setTotalReservasConsumadas(objRAceptadas.getInt("reservas"));
                fac.setTotalConsumadas(objRAceptadas.getFloat("importeTotal"));
               
            } else { //El cliente no tiene ninguna reserva consumada
                res.setTotalReservasConsumadas(0);
                fac.setTotalConsumadas(new Float(0.00));             
                                
            }
            // Obtenemos los datos de reservas consumadas
            String sSQL3 = "select count(r.idreserva) as reservas, sum(r.importe) as importeTotal "
                    + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                    + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                    + "a.idaula="+idAula+" and r.estado="+Constantes.RESERVA_CANCELADA+" and "
                    + "a.idciudad=c.idciudad and "
                    + condicionLocalizacion;
            
            ResultSet objRCanceladas = super.obtenerConsulta(sSQL3);
            //solo puede haber un resultado o el valor será 0
            if (objRCanceladas.next()) {
                res.setTotalReservasCanceladas(objRCanceladas.getInt("reservas"));
                fac.setTotalCanceladas(objRCanceladas.getFloat("importeTotal"));
               
            } else { //El cliente no tiene ninguna reserva consumada
                res.setTotalReservasCanceladas(0);
                fac.setTotalCanceladas(new Float(0.00));             
                                
            }
           // Liberamos recursos
           objRCanceladas.close();
           objRCanceladas = null;
           objRAceptadas.close();
           objRAceptadas = null;
           
            //totales, atendidas y canceladas
            rentabilidad.setReservas(res);
            rentabilidad.setFacturacion(fac);
            rentabilidad.setFacturacionMedia(facMedia);

            lista.add(rentabilidad);
            
            //comprobamos si seguimos obteniendo filas
            if(top==Constantes.TOP_TODOS) {
                continuar = true;
            } else if ((top==Constantes.TOP_10)||(top==Constantes.TOP_20)) {               
                numFilas++;
                continuar = numFilas < top;
            } 
        }
        
        // Liberamos recursos
        objRS.close();
        objRS = null;
        
      } catch (SQLException eS) {
            throw new DataBaseDAOException(DataBaseDAOException.CAN_NOT_EXECUTEQUERY
                    + Constantes.SL + eS.getMessage());
      }
      return lista;
    }


    @Override
    public List<RentabilidadClienteDTO> getRentabilidadClientes(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int tipoLocalizacion, int top) throws DataBaseDAOException {
  
        List<RentabilidadClienteDTO> rentabilidad = null;
        try {
            if(tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PAISES) {
                rentabilidad = getRentabilidadClientesPaises (filtroLoc, filtroFechas, top);
            }else if (tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PROVINCIAS) {
                rentabilidad = getRentabilidadClienteProvincias (filtroLoc, filtroFechas, top);
            }else {
                rentabilidad = getRentabilidadClienteCiudades (filtroLoc, filtroFechas, top);
            }
        } catch (SQLException eS) {
            throw new DataBaseDAOException(DataBaseDAOException.CAN_NOT_EXECUTEQUERY
                    + Constantes.SL + eS.getMessage());
        }            
        return rentabilidad;

    }

    @Override
    public List<RentabilidadLocalizacionDTO> getRentabilidadLocalizacion(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int tipoLocalizacion, int top) throws DataBaseDAOException {
      
      List<RentabilidadLocalizacionDTO> lista = new ArrayList<RentabilidadLocalizacionDTO>();
      try {       
        //Siempre tienen que llegar fechas, si el usuario no indica ninguna, presentación
        //deberá añadir unas por defecto, ejemplo inicio de la aplicación hasta el día actual
        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String fechaDesde = formato.format(filtroFechas.getFechaInicio());
        String fechaHasta = formato.format(filtroFechas.getFechaFin());

        // Construimos la lista de localizaciones para obtener solo esas aulas
        String condicionLocalizacion="";
        String condicionIndividualLoc="";
        String tablasLocalizacion="";
        String localizacion="";
        String agrupacion="";
        if (tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PAISES) {
            String paises = obtenerListaPaises(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idpais=ps.idpais and ps.idpais in ("+paises+")";
            condicionIndividualLoc = "a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idpais=ps.idpais and ps.idpais=";
            tablasLocalizacion = "ciudad c, provincia p, pais ps ";
            localizacion = "ps.nombre as localizacion, ps.idpais as idLoc";
            agrupacion = "group by ps.nombre, ps.idpais";
            
        } else if (tipoLocalizacion==Constantes.TIPO_LOCALIZACION_PROVINCIAS) {
            String provincias = obtenerListaProvincias(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+")";
            condicionIndividualLoc = "a.idciudad=c.idciudad and c.idprovincia=";
            tablasLocalizacion = "provincia p, ciudad c ";
            localizacion = "p.nombre as localizacion, p.idprovincia as idLoc";
            agrupacion = "group by p.nombre, p.idprovincia";
        } else {
            String ciudades = obtenerListaCiudades(filtroLoc);
            condicionLocalizacion = "a.idciudad=c.idciudad and c.idciudad in ("+ciudades+")";
            condicionIndividualLoc = "a.idciudad=";
            tablasLocalizacion = "ciudad c ";
            localizacion = "c.nombre as localizacion, c.idciudad as idLoc";
            agrupacion = "group by c.nombre,c.idciudad";
        }
        
        //Obtenemos los datos de las medias globales para calcular la posición del aula respecto a ella
        //Los valores globales será la media de asistentes/número de reservas totales y la facturación total/número de reservas
        //Las reservas pueden estar consumadas o canceladas, ya que si estuvieran pendientes aún no se sabría el importe que
        //se va a recibir de ellas
        String sSQLGlobal = "select sum(importe) as importeTotal, sum(asistentes) as asistentes, count(idreserva) as reservas "
                + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and  "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and "
                + "(r.estado="+Constantes.RESERVA_CONSUMADA+" or r.estado="+Constantes.RESERVA_CANCELADA+") and "
                + condicionLocalizacion;
        
        
        ResultSet objRSGlobal = super.obtenerConsulta(sSQLGlobal);
        Float mediaGlobalAsistentes = new Float(0.00);
        Float mediaGlobalFacturacion = new Float(0.00);
        if(objRSGlobal.next()) {
            mediaGlobalAsistentes = (objRSGlobal.getFloat("asistentes")) / (objRSGlobal.getFloat("reservas"));
            mediaGlobalFacturacion = (objRSGlobal.getFloat("importeTotal")) / (objRSGlobal.getFloat("reservas"));
        }

        // Liberamos recursos
        objRSGlobal.close();
        objRSGlobal = null;
        
        //Datos globales de localizaciones - solo cuenta consumadas o canceladas, reservas pendientes no se han cobrado todavía
        String sSQL = "select "+localizacion+", count(s.idcliente) as clientes, count(r.idreserva) as reservas, "
                + "sum(importe) as importeTotal, sum(asistentes) as asistentes "
                + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                + "(s.fechaini>='"+fechaDesde+"' and s.fechafin<='"+fechaHasta+"') and "
                + "(r.estado="+Constantes.RESERVA_CONSUMADA+" or r.estado="+Constantes.RESERVA_CANCELADA+") and "
                + condicionLocalizacion + " "                
                + agrupacion;
       
        
        ResultSet objRS = super.obtenerConsulta(sSQL);
        Integer asistentes, reservas, idLoc;
        Float importe = new Float(0.00);
        boolean continuar = true; //variable que controla el número de filas a obtener según el valor de Top
        int numFilas = 0;        
        while (objRS.next()&&continuar) { //por cada localización, casos particulares reservas consumadas vs reservas canceladas

            RentabilidadLocalizacionDTO rentabilidad = new RentabilidadLocalizacionDTO();
            rentabilidad.setLocalizacion(objRS.getString("localizacion"));
            idLoc = objRS.getInt("idLoc");
            rentabilidad.setTipoLocalizacion(tipoLocalizacion);
            
            RentabilidadReservasDTO res = new RentabilidadReservasDTO();
            reservas = objRS.getInt("reservas");
            res.setTotalReservas(reservas);
            
            asistentes = objRS.getInt("asistentes");
            rentabilidad.setAsistentes(asistentes);
            rentabilidad.setClientes(objRS.getInt("clientes"));
            
            RentabilidadFacturacionDTO fac = new RentabilidadFacturacionDTO();
            RentabilidadFacturacionMediaDTO facMedia = new RentabilidadFacturacionMediaDTO();
            importe = objRS.getFloat("importeTotal");
            fac.setTotal(importe);
            
            //solo puede haber un resultado o el valor será 0
            Float mediaAsistentes = new Float(0.00);
            Float mediaFacturacion = new Float(0.00);
            if((reservas!=0)&&(asistentes!=0))
                mediaAsistentes = new Float(asistentes / reservas);
            if((reservas!=0)&&(importe!=0))
                mediaFacturacion = new Float(importe / reservas);
            rentabilidad.setMediaAsistentes(mediaAsistentes);
            facMedia.setMedia(mediaFacturacion);
            
            Float diferencia = new Float(0.00);
            //Calculamos las diferencias con respecto a los datos globales, media de asistentes y media de facturación
            if(mediaGlobalFacturacion==0) {//si la global fuera 0 porque aún no hay reservas consumadas o canceladas la diferencia también será 0.00
                    facMedia.setDiferencia(diferencia);
            }else {
                    if(mediaFacturacion>=mediaGlobalFacturacion) { //diferencia positiva
                        diferencia = (mediaFacturacion * 100) / mediaGlobalFacturacion; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaFacturacion * 100) / mediaGlobalFacturacion);
                    }
                    facMedia.setDiferencia(diferencia);
            }
            if(mediaGlobalAsistentes==0) {//si la global fuera 0 porque aún no hay reservas consumadas o canceladas la diferencia también será 0.00
                    rentabilidad.setDiferenciaMediaAsistentes(mediaGlobalAsistentes);
            }else {
                    diferencia = new Float(0.00);
                    if(mediaAsistentes>=mediaGlobalAsistentes) { //diferencia positiva
                        diferencia = (mediaAsistentes * 100) / mediaGlobalAsistentes; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaAsistentes * 100) / mediaGlobalAsistentes);
                    }
                    rentabilidad.setDiferenciaMediaAsistentes(diferencia);
            }           
            // Obtenemos los datos de reservas consumadas
            String sSQL2 = "select count(r.idreserva) as reservas, sum(r.importe) as importeTotal "
                    + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                    + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                    + "r.estado="+Constantes.RESERVA_CONSUMADA+" and "
                    + "a.idciudad=c.idciudad and "
                    + condicionIndividualLoc + idLoc;
          
            ResultSet objRAceptadas = super.obtenerConsulta(sSQL2);
            //solo puede haber un resultado o el valor será 0
            if (objRAceptadas.next()) {
                res.setTotalReservasConsumadas(objRAceptadas.getInt("reservas"));
                fac.setTotalConsumadas(objRAceptadas.getFloat("importeTotal"));
               
            } else { //El cliente no tiene ninguna reserva consumada
                res.setTotalReservasConsumadas(0);
                fac.setTotalConsumadas(new Float(0.00));             
                                
            }
            // Obtenemos los datos de reservas consumadas
            String sSQL3 = "select count(r.idreserva) as reservas, sum(r.importe) as importeTotal "
                    + "from reserva r, solicitud s, aula a, "+tablasLocalizacion
                    + "where r.idaula=a.idaula and r.idreserva=s.idsolicitud and "
                    + "r.estado="+Constantes.RESERVA_CANCELADA+" and "
                    + "a.idciudad=c.idciudad and "
                    + condicionIndividualLoc + idLoc;
            
            ResultSet objRCanceladas = super.obtenerConsulta(sSQL3);
            //solo puede haber un resultado o el valor será 0
            if (objRCanceladas.next()) {
                res.setTotalReservasCanceladas(objRCanceladas.getInt("reservas"));
                fac.setTotalCanceladas(objRCanceladas.getFloat("importeTotal"));
               
            } else { //El cliente no tiene ninguna reserva consumada
                res.setTotalReservasCanceladas(0);
                fac.setTotalCanceladas(new Float(0.00));             
                                
            }

           // Liberamos recursos
           objRCanceladas.close();
           objRCanceladas = null;
           objRAceptadas.close();
           objRAceptadas = null;
           
            //totales, atendidas y canceladas
            rentabilidad.setReservas(res);
            rentabilidad.setFacturacion(fac);
            rentabilidad.setFacturacionMedia(facMedia);

            lista.add(rentabilidad);
            //comprobamos si seguimos obteniendo filas
            if(top==Constantes.TOP_TODOS) {
                continuar = true;
            } else if ((top==Constantes.TOP_10)||(top==Constantes.TOP_20)) {               
                numFilas++;
                continuar = numFilas < top;
            }             
        }
        // Liberamos recursos
        objRS.close();
        objRS = null;        
        
      } catch (SQLException eS) {
            throw new DataBaseDAOException(DataBaseDAOException.CAN_NOT_EXECUTEQUERY
                    + Constantes.SL + eS.getMessage());
      }
      
      return lista;
    }

    private List<RentabilidadClienteDTO> getRentabilidadClientesPaises(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int top) throws DataBaseDAOException, SQLException   {
        
        List<RentabilidadClienteDTO> lista = new ArrayList<RentabilidadClienteDTO>();

        //Por cada cliente se calculan los campos totales, los campos de solicitudes/reservas atendidas/consumadas
        //y por último los campos de solicitudes/reservas canceladas/canceladas
                
        //Siempre tienen que llegar fechas, si el usuario no indica ninguna, presentación
        //deberá añadir unas por defecto, ejemplo inicio de la aplicación hasta el día actual
        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String fechaDesde = formato.format(filtroFechas.getFechaInicio());
        String fechaHasta = formato.format(filtroFechas.getFechaFin());

        //Países seleccionados
        List<PaisDTO> listaPaises = filtroLoc.getPaises();

        // Construimos la lista de idciudad para obtener solo esas aulas
        String paises = "";
        PaisDTO pais = null;
        for (int i = 0; i < listaPaises.size(); i++) {
            pais = listaPaises.get(i);
            if (i == 0) {
                paises = pais.getIdPais().toString();
            } else {
                paises += ",'" + pais.getIdPais().toString() + "'";
            }
        }
        
        //Obtenemos los datos de las medias globales para calcular la posición del cliente respecto a ella
        //Dado que el %importe/reservas puede calcularse directamente en pantallas
        //los valores globales de las medias serán de las aceptadas y de las canceladas
        String sSQLGlobal = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a, ciudad c, provincia p "
                + "where a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idpais in ("+paises+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud and "
                + "r.estado="+Constantes.RESERVA_CONSUMADA;
        
        ResultSet objRSGlobal = super.obtenerConsulta(sSQLGlobal);
        Float mediaGlobal = new Float(0.00);
        if(objRSGlobal.next()) {
            mediaGlobal = (objRSGlobal.getFloat("importeTotal")) / (objRSGlobal.getFloat("totalReservas"));
        }
        
        String sSQLGlobalCanceladas = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a, ciudad c, provincia p "
                + "where a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idpais in ("+paises+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud "
                + "and r.estado="+Constantes.RESERVA_CANCELADA;
        ResultSet objRSGlobalCanceladas = super.obtenerConsulta(sSQLGlobalCanceladas);
        Float mediaGlobalCanceladas = new Float(0.00);
        if(objRSGlobalCanceladas.next()) {
            mediaGlobalCanceladas = (objRSGlobalCanceladas.getFloat("importeTotal")) / (objRSGlobalCanceladas.getFloat("totalReservas"));            
        }
        // Liberamos recursos
        objRSGlobal.close();
        objRSGlobal = null;        
        //Datos globales de un cliente
        String sSQL = "SELECT u.idusuario as idCliente, u.nombre as cliente, count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, count(a.idaula) as totalAulas,sum(importe) as totalImporte "
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and "
                + "(s.fechaini>='"+fechaDesde+"' and s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia = p.idprovincia and p.idpais in ("+paises+") "
                + "group by u.idusuario,u.nombre";
        
        ResultSet objRS = super.obtenerConsulta(sSQL);
        String idcliente;
        boolean continuar = true; //variable que controla el número de filas a obtener según el valor de Top
        int numFilas = 0;
        
        while (objRS.next()&&continuar) {//por cliente

            RentabilidadClienteDTO rentabilidad = new RentabilidadClienteDTO();
            idcliente = objRS.getString("idCliente");
            rentabilidad.setCliente(objRS.getString("cliente"));
            
            RentabilidadSolicitudesDTO sol = new RentabilidadSolicitudesDTO();
            sol.setTotal(objRS.getInt("totalSolicitudes"));
            
            RentabilidadReservasDTO res = new RentabilidadReservasDTO();
            res.setTotalReservas(objRS.getInt("totalReservas"));
            
            rentabilidad.setTotalAulas(objRS.getInt("totalAulas"));
            
            RentabilidadFacturacionDTO fac = new RentabilidadFacturacionDTO();
            RentabilidadFacturacionMediaDTO facMedia = new RentabilidadFacturacionMediaDTO();
            fac.setTotal(objRS.getFloat("totalImporte"));
            
            // Obtenemos los datos de aceptadas, consumadas
            String sSQL2 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia = p.idprovincia and p.idpais in ("+paises+") and "
                + "s.estado="+Constantes.SOLICITUD_ATENDIDA+" and r.estado="+Constantes.RESERVA_CONSUMADA+" group by u.idusuario";
            
            ResultSet objRAceptadas = super.obtenerConsulta(sSQL2);
            //solo puede haber un resultado o el valor será 0
            Float mediaCliente = new Float(0.00);
            Float diferencia = new Float(0.00);
            if (objRAceptadas.next()) {
                sol.setTotalAtendidas(objRAceptadas.getInt("totalSolicitudes"));
                res.setTotalReservasConsumadas(objRAceptadas.getInt("totalReservas"));
                fac.setTotalConsumadas(objRAceptadas.getFloat("totalImporte"));
                mediaCliente = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReserva(mediaCliente);
                facMedia.setMedia(mediaCliente);
                //La diferencia de la media del cliente con respecto a la media del global
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobal==0) {//si la global fuera 0 porque aún no hay reservas consumadas la diferencia también será 0.00
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }else {
                    if(mediaCliente>=mediaGlobal) { //diferencia positiva
                        diferencia = (mediaCliente * 100) / mediaGlobal; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaCliente * 100) / mediaGlobal);
                    }
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva consumada
                sol.setTotalAtendidas(0);
                res.setTotalReservasConsumadas(0);
                fac.setTotalConsumadas(new Float(0.00));
                //res.setMediaFactReserva(mediaCliente); //es 0.00
                facMedia.setMedia(mediaCliente);
                if(mediaGlobal==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva consumada
                    //res.setDifMediaFactReserva(diferencia); //es 0.00
                    facMedia.setDiferencia(diferencia);
                }else {
                    //res.setDifMediaFactReserva(new Float(-100.00)); //La diferencia sería el total, -100%
                    facMedia.setDiferencia(new Float(-100.00));//La diferencia sería el total, -100%
                }               
                                
            }
            
            String sSQL3 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia = p.idprovincia and p.idpais in ("+paises+") and "
                + "s.estado="+Constantes.SOLICITUD_CANCELADA+" and r.estado="+Constantes.RESERVA_CANCELADA+" group by u.idusuario";
                    
            ResultSet objRCancel = super.obtenerConsulta(sSQL3);
            Float mediaClienteCancel = new Float(0.00);
            //solo puede haber un resultado o el valor será 0
            diferencia = new Float(0.00);
            if (objRCancel.next()) {
                sol.setTotalCanceladas(objRCancel.getInt("totalSolicitudes"));
                res.setTotalReservasCanceladas(objRCancel.getInt("totalReservas"));
                fac.setTotalCanceladas(objRCancel.getFloat("totalImporte"));
                mediaClienteCancel = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReservaCancel(mediaClienteCancel);
                rentabilidad.setMediaCancelacion(mediaClienteCancel);
                //La diferencia de la media del cliente con respecto a la media del global de canceladas
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobalCanceladas==0) {//si la global fuera 0 porque aún no hay reservas canceladas la diferencia también será 0.00
                    //res.setMediaFactReservaCancel(diferencia);
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }else {
                    if(mediaClienteCancel>=mediaGlobalCanceladas) { //diferencia positiva
                        diferencia = (mediaClienteCancel * 100) / mediaGlobalCanceladas; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaClienteCancel * 100) / mediaGlobalCanceladas);
                    }
                    //res.setDifMediaFactReserva(diferencia);
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva cancelada
                sol.setTotalCanceladas(0);
                res.setTotalReservasCanceladas(0);
                fac.setTotalCanceladas(new Float(0.00));
                //res.setMediaFactReservaCancel(mediaCliente); //es 0.00
                rentabilidad.setMediaCancelacion(mediaCliente);
                if(mediaGlobalCanceladas==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva cancelada
                    //res.setDifMediaFactReservaCancel(diferencia); //es 0.00
                    rentabilidad.setDiferenciaMediaCancelacion (diferencia);
                }else {
                    //res.setDifMediaFactReservaCancel(new Float(-100.00)); //La diferencia sería el total, -100%
                    rentabilidad.setDiferenciaMediaCancelacion (new Float(-100.00));
                }               
                                
            }          
            
            //totales, atendidas y canceladas
            rentabilidad.setSolicitudes(sol);
            rentabilidad.setReservas(res);
            rentabilidad.setFacturacion(fac);
            rentabilidad.setFacturacionMedia(facMedia);

           // Liberamos recursos
           objRCancel.close();
           objRCancel = null;
           objRAceptadas.close();
           objRAceptadas = null;            
            lista.add(rentabilidad);
            //comprobamos si seguimos obteniendo filas
            if(top==Constantes.TOP_TODOS) {
                continuar = true;
            } else if ((top==Constantes.TOP_10)||(top==Constantes.TOP_20)) {               
                numFilas++;
                continuar = numFilas < top;
            } 
            
        }
        // Liberamos recursos
        objRS.close();
        objRS = null;
        
        return lista;
    }

    private List<RentabilidadClienteDTO> getRentabilidadClienteProvincias(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int top) throws DataBaseDAOException, SQLException {
        
        List<RentabilidadClienteDTO> lista = new ArrayList<RentabilidadClienteDTO>();

        //Por cada cliente se calculan los campos totales, los campos de solicitudes/reservas atendidas/consumadas
        //y por último los campos de solicitudes/reservas canceladas/canceladas
                
        //Siempre tienen que llegar fechas, si el usuario no indica ninguna, presentación
        //deberá añadir unas por defecto, ejemplo inicio de la aplicación hasta el día actual
        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String fechaDesde = formato.format(filtroFechas.getFechaInicio());
        String fechaHasta = formato.format(filtroFechas.getFechaFin());

        //Países seleccionados
        List<ProvinciaDTO> listaProvincias = filtroLoc.getProvincias();

        // Construimos la lista de idciudad para obtener solo esas aulas
        String provincias = "";
        ProvinciaDTO provincia = null;
        for (int i = 0; i < listaProvincias.size(); i++) {
            provincia = listaProvincias.get(i);
            if (i == 0) {
                provincias = provincia.getIdProvincia().toString();
            } else {
                provincias += ",'" + provincia.getIdProvincia().toString() + "'";
            }
        }
        
        //Obtenemos los datos de las medias globales para calcular la posición del cliente respecto a ella
        //Dado que el %importe/reservas puede calcularse directamente en pantallas
        //los valores globales de las medias serán de las aceptadas y de las canceladas
        String sSQLGlobal = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a, ciudad c, provincia p "
                + "where a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud and "
                + "r.estado="+Constantes.RESERVA_CONSUMADA;
        
        ResultSet objRSGlobal = super.obtenerConsulta(sSQLGlobal);
        Float mediaGlobal = new Float(0.00);
        if(objRSGlobal.next()) {
            mediaGlobal = (objRSGlobal.getFloat("importeTotal")) / (objRSGlobal.getFloat("totalReservas"));
        }
        
        String sSQLGlobalCanceladas = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a, ciudad c, provincia p "
                + "where a.idciudad=c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud "
                + "and r.estado="+Constantes.RESERVA_CANCELADA;
        ResultSet objRSGlobalCanceladas = super.obtenerConsulta(sSQLGlobalCanceladas);
        Float mediaGlobalCanceladas = new Float(0.00);
        if(objRSGlobalCanceladas.next()) {
            mediaGlobalCanceladas = (objRSGlobalCanceladas.getFloat("importeTotal")) / (objRSGlobalCanceladas.getFloat("totalReservas"));            
        }
        // Liberamos recursos
        objRSGlobal.close();
        objRSGlobal = null;
        objRSGlobalCanceladas.close();
        objRSGlobalCanceladas = null;
        
        //Datos globales de un cliente
        String sSQL = "SELECT u.idusuario as idCliente, u.nombre as cliente, count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, count(a.idaula) as totalAulas,sum(importe) as totalImporte "
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and "
                + "(s.fechaini>='"+fechaDesde+"' and s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+") "
                + "group by u.idusuario,u.nombre";
        
        ResultSet objRS = super.obtenerConsulta(sSQL);
        String idcliente;
        boolean continuar = true; //variable que controla el número de filas a obtener según el valor de Top
        int numFilas = 0;
        
        while (objRS.next()&&continuar) {//por cliente

            RentabilidadClienteDTO rentabilidad = new RentabilidadClienteDTO();
            idcliente = objRS.getString("idCliente");
            rentabilidad.setCliente(objRS.getString("cliente"));
            
            RentabilidadSolicitudesDTO sol = new RentabilidadSolicitudesDTO();
            sol.setTotal(objRS.getInt("totalSolicitudes"));
            
            RentabilidadReservasDTO res = new RentabilidadReservasDTO();
            res.setTotalReservas(objRS.getInt("totalReservas"));
            
            rentabilidad.setTotalAulas(objRS.getInt("totalAulas"));
            
            RentabilidadFacturacionDTO fac = new RentabilidadFacturacionDTO();
            RentabilidadFacturacionMediaDTO facMedia = new RentabilidadFacturacionMediaDTO();
            fac.setTotal(objRS.getFloat("totalImporte"));
            
            // Obtenemos los datos de aceptadas, consumadas
            String sSQL2 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+") and "
                + "s.estado="+Constantes.SOLICITUD_ATENDIDA+" and r.estado="+Constantes.RESERVA_CONSUMADA+" group by u.idusuario";
            
            ResultSet objRAceptadas = super.obtenerConsulta(sSQL2);
            //solo puede haber un resultado o el valor será 0
            Float mediaCliente = new Float(0.00);
            Float diferencia = new Float(0.00);
            if (objRAceptadas.next()) {
                sol.setTotalAtendidas(objRAceptadas.getInt("totalSolicitudes"));
                res.setTotalReservasConsumadas(objRAceptadas.getInt("totalReservas"));
                fac.setTotalConsumadas(objRAceptadas.getFloat("totalImporte"));
                mediaCliente = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReserva(mediaCliente);
                facMedia.setMedia(mediaCliente);
                //La diferencia de la media del cliente con respecto a la media del global
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobal==0) {//si la global fuera 0 porque aún no hay reservas consumadas la diferencia también será 0.00
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }else {
                    if(mediaCliente>=mediaGlobal) { //diferencia positiva
                        diferencia = (mediaCliente * 100) / mediaGlobal; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaCliente * 100) / mediaGlobal);
                    }
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva consumada
                sol.setTotalAtendidas(0);
                res.setTotalReservasConsumadas(0);
                fac.setTotalConsumadas(new Float(0.00));
                //res.setMediaFactReserva(mediaCliente); //es 0.00
                facMedia.setMedia(mediaCliente);
                if(mediaGlobal==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva consumada
                    //res.setDifMediaFactReserva(diferencia); //es 0.00
                    facMedia.setDiferencia(diferencia);
                }else {
                    //res.setDifMediaFactReserva(new Float(-100.00)); //La diferencia sería el total, -100%
                    facMedia.setDiferencia(new Float(-100.00));
                }               
                                
            }
            
            String sSQL3 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u, ciudad c, provincia p "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad = c.idciudad and c.idprovincia=p.idprovincia and p.idprovincia in ("+provincias+") and "
                + "s.estado="+Constantes.SOLICITUD_CANCELADA+" and r.estado="+Constantes.RESERVA_CANCELADA+" group by u.idusuario";
                    
            ResultSet objRCancel = super.obtenerConsulta(sSQL3);
            Float mediaClienteCancel = new Float(0.00);
            //solo puede haber un resultado o el valor será 0
            diferencia = new Float(0.00);
            if (objRCancel.next()) {
                sol.setTotalCanceladas(objRCancel.getInt("totalSolicitudes"));
                res.setTotalReservasCanceladas(objRCancel.getInt("totalReservas"));
                fac.setTotalCanceladas(objRCancel.getFloat("totalImporte"));
                mediaClienteCancel = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReservaCancel(mediaClienteCancel);
                rentabilidad.setMediaCancelacion(mediaClienteCancel);
                //La diferencia de la media del cliente con respecto a la media del global de canceladas
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobalCanceladas==0) {//si la global fuera 0 porque aún no hay reservas canceladas la diferencia también será 0.00
                    //res.setMediaFactReservaCancel(diferencia);
                    rentabilidad.setMediaCancelacion(diferencia);
                }else {
                    if(mediaClienteCancel>=mediaGlobalCanceladas) { //diferencia positiva
                        diferencia = (mediaClienteCancel * 100) / mediaGlobalCanceladas; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaClienteCancel * 100) / mediaGlobalCanceladas);
                    }
                    //res.setDifMediaFactReserva(diferencia);
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva cancelada
                sol.setTotalCanceladas(0);
                res.setTotalReservasCanceladas(0);
                fac.setTotalCanceladas(new Float(0.00));
                //res.setMediaFactReservaCancel(mediaCliente); //es 0.00
                rentabilidad.setMediaCancelacion(mediaClienteCancel);
                if(mediaGlobalCanceladas==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva cancelada
                    //res.setDifMediaFactReservaCancel(diferencia); //es 0.00
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }else {
                    //res.setDifMediaFactReservaCancel(new Float(-100.00)); //La diferencia sería el total, -100%
                    rentabilidad.setDiferenciaMediaCancelacion(new Float(-100.00));
                }               
                                
            }          
           // Liberamos recursos
           objRCancel.close();
           objRCancel = null;
           objRAceptadas.close();
           objRAceptadas = null;
           
            //totales, atendidas y canceladas
            rentabilidad.setSolicitudes(sol);
            rentabilidad.setReservas(res);
            rentabilidad.setFacturacion(fac);
            rentabilidad.setFacturacionMedia(facMedia);

            lista.add(rentabilidad);

            //comprobamos si seguimos obteniendo filas
            if(top==Constantes.TOP_TODOS) {
                continuar = true;
            } else if ((top==Constantes.TOP_10)||(top==Constantes.TOP_20)) {               
                numFilas++;
                continuar = numFilas < top;
            }             
            
        }
        // Liberamos recursos
        objRS.close();
        objRS = null;
        
        return lista;
    }

    private List<RentabilidadClienteDTO> getRentabilidadClienteCiudades(FiltroLocalizacion filtroLoc, FiltroFechas filtroFechas, int top) throws DataBaseDAOException, SQLException {
        
        List<RentabilidadClienteDTO> lista = new ArrayList<RentabilidadClienteDTO>();

        //Por cada cliente se calculan los campos totales, los campos de solicitudes/reservas atendidas/consumadas
        //y por último los campos de solicitudes/reservas canceladas/canceladas
                
        //Siempre tienen que llegar fechas, si el usuario no indica ninguna, presentación
        //deberá añadir unas por defecto, ejemplo inicio de la aplicación hasta el día actual
        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String fechaDesde = formato.format(filtroFechas.getFechaInicio());
        String fechaHasta = formato.format(filtroFechas.getFechaFin());

        //Ciudades seleccionados
        List<CiudadDTO> listaCiudades = filtroLoc.getCiudades();

        // Construimos la lista de idciudad para obtener solo esas aulas
        String ciudades = "";
        CiudadDTO ciudad = null;
        for (int i = 0; i < listaCiudades.size(); i++) {
            ciudad = listaCiudades.get(i);
            if (i == 0) {
                ciudades = ciudad.getIdCiudad().toString();
            } else {
                ciudades += ",'" + ciudad.getIdCiudad().toString() + "'";
            }
        }
        
        //Obtenemos los datos de las medias globales para calcular la posición del cliente respecto a ella
        //Dado que el %importe/reservas puede calcularse directamente en pantallas
        //los valores globales de las medias serán de las aceptadas y de las canceladas
        String sSQLGlobal = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a "
                + "where a.idciudad in ("+ciudades+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud and "
                + "r.estado="+Constantes.RESERVA_CONSUMADA;
        
        ResultSet objRSGlobal = super.obtenerConsulta(sSQLGlobal);
        Float mediaGlobal = new Float(0.00);
        if(objRSGlobal.next()) {
            mediaGlobal = (objRSGlobal.getFloat("importeTotal")) / (objRSGlobal.getFloat("totalReservas"));
        }
        
        String sSQLGlobalCanceladas = "select sum(importe) as importeTotal, count(idReserva) as totalReservas "
                + "from reserva r, solicitud s "
                + "where r.idaula in (select a.idaula from aula a "
                + "where a.idciudad in ("+ciudades+")) "
                + "and (s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idreserva = s.idsolicitud "
                + "and r.estado="+Constantes.RESERVA_CANCELADA;
        ResultSet objRSGlobalCanceladas = super.obtenerConsulta(sSQLGlobalCanceladas);
        Float mediaGlobalCanceladas = new Float(0.00);
        if(objRSGlobalCanceladas.next()) {
            mediaGlobalCanceladas = (objRSGlobalCanceladas.getFloat("importeTotal")) / (objRSGlobalCanceladas.getFloat("totalReservas"));            
        }
        // Liberamos recursos
        objRSGlobal.close();
        objRSGlobal = null;
        objRSGlobalCanceladas.close();
        objRSGlobalCanceladas = null;
        
        //Datos globales de un cliente
        String sSQL = "SELECT u.idusuario as idCliente, u.nombre as cliente, count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, count(a.idaula) as totalAulas,sum(importe) as totalImporte "
                + "from reserva r,solicitud s,aula a,usuario u "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and "
                + "(s.fechaini>='"+fechaDesde+"' and s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad in ("+ciudades+") "
                + "group by u.idusuario,u.nombre";
        
        ResultSet objRS = super.obtenerConsulta(sSQL);
        String idcliente;
        boolean continuar = true; //variable que controla el número de filas a obtener según el valor de Top
        int numFilas = 0;
        
        while (objRS.next()&&continuar) {//por cliente

            RentabilidadClienteDTO rentabilidad = new RentabilidadClienteDTO();
            idcliente = objRS.getString("idCliente");
            rentabilidad.setCliente(objRS.getString("cliente"));
            
            RentabilidadSolicitudesDTO sol = new RentabilidadSolicitudesDTO();
            sol.setTotal(objRS.getInt("totalSolicitudes"));
            
            RentabilidadReservasDTO res = new RentabilidadReservasDTO();
            res.setTotalReservas(objRS.getInt("totalReservas"));
            
            rentabilidad.setTotalAulas(objRS.getInt("totalAulas"));
            
            RentabilidadFacturacionDTO fac = new RentabilidadFacturacionDTO();
            RentabilidadFacturacionMediaDTO facMedia = new RentabilidadFacturacionMediaDTO();
            fac.setTotal(objRS.getFloat("totalImporte"));
            
            // Obtenemos los datos de aceptadas, consumadas
            String sSQL2 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad in ("+ciudades+") and "
                + "s.estado="+Constantes.SOLICITUD_ATENDIDA+" and r.estado="+Constantes.RESERVA_CONSUMADA+" group by u.idusuario";
            
            ResultSet objRAceptadas = super.obtenerConsulta(sSQL2);
            //solo puede haber un resultado o el valor será 0
            Float mediaCliente = new Float(0.00);
            Float diferencia = new Float(0.00);
            if (objRAceptadas.next()) {
                sol.setTotalAtendidas(objRAceptadas.getInt("totalSolicitudes"));
                res.setTotalReservasConsumadas(objRAceptadas.getInt("totalReservas"));
                fac.setTotalConsumadas(objRAceptadas.getFloat("totalImporte"));
                mediaCliente = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReserva(mediaCliente);
                facMedia.setMedia(mediaCliente);
                //La diferencia de la media del cliente con respecto a la media del global
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobal==0) {//si la global fuera 0 porque aún no hay reservas consumadas la diferencia también será 0.00
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }else {
                    if(mediaCliente>=mediaGlobal) { //diferencia positiva
                        diferencia = (mediaCliente * 100) / mediaGlobal; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaCliente * 100) / mediaGlobal);
                    }
                    //res.setDifMediaFactReserva(diferencia);
                    facMedia.setDiferencia(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva consumada
                sol.setTotalAtendidas(0);
                res.setTotalReservasConsumadas(0);
                fac.setTotalConsumadas(new Float(0.00));
                //res.setMediaFactReserva(mediaCliente); //es 0.00
                facMedia.setMedia(mediaCliente);
                if(mediaGlobal==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva consumada
                    //res.setDifMediaFactReserva(diferencia); //es 0.00
                    facMedia.setDiferencia(diferencia);
                }else {
                    //res.setDifMediaFactReserva(new Float(-100.00)); //La diferencia sería el total, -100%
                    facMedia.setDiferencia(new Float(-100.00));
                }               
                                
            }
            
            String sSQL3 = "SELECT count(idsolicitud) as totalSolicitudes, "
                + "count(idreserva) as totalReservas, sum(importe) as totalImporte " 
                + "from reserva r,solicitud s,aula a,usuario u "
                + "where s.idsolicitud=r.idreserva and s.idcliente=u.idusuario and u.idusuario='"+idcliente+"' and "
                + "(s.fechaini>='"+fechaDesde+"' or s.fechafin<='"+fechaHasta+"') and r.idaula=a.idaula and "
                + "a.idciudad in ("+ciudades+") and "
                + "s.estado="+Constantes.SOLICITUD_CANCELADA+" and r.estado="+Constantes.RESERVA_CANCELADA+" group by u.idusuario";
                    
            ResultSet objRCancel = super.obtenerConsulta(sSQL3);
            Float mediaClienteCancel = new Float(0.00);
            //solo puede haber un resultado o el valor será 0
            diferencia = new Float(0.00);
            if (objRCancel.next()) {
                sol.setTotalCanceladas(objRCancel.getInt("totalSolicitudes"));
                res.setTotalReservasCanceladas(objRCancel.getInt("totalReservas"));
                fac.setTotalCanceladas(objRCancel.getFloat("totalImporte"));
                mediaClienteCancel = (objRAceptadas.getFloat("totalImporte")/objRAceptadas.getInt("totalReservas"));
                //res.setMediaFactReservaCancel(mediaClienteCancel);
                rentabilidad.setMediaCancelacion(mediaClienteCancel);
                //La diferencia de la media del cliente con respecto a la media del global de canceladas
                //Cuando la media del cliente es > a la global se grabará una diferencia positiva
                //y cuando la media del cliente es < a la global se grabará una diferencia negativa
                if(mediaGlobalCanceladas==0) {//si la global fuera 0 porque aún no hay reservas canceladas la diferencia también será 0.00
                    //res.setMediaFactReservaCancel(diferencia);
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }else {
                    if(mediaClienteCancel>=mediaGlobalCanceladas) { //diferencia positiva
                        diferencia = (mediaClienteCancel * 100) / mediaGlobalCanceladas; 
                    }else {
                        //diferencia negativa
                        diferencia = -((mediaClienteCancel * 100) / mediaGlobalCanceladas);
                    }
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia);
                }
               
            } else { //El cliente no tiene ninguna reserva cancelada
                sol.setTotalCanceladas(0);
                res.setTotalReservasCanceladas(0);
                fac.setTotalCanceladas(new Float(0.00));
                //res.setMediaFactReservaCancel(mediaCliente); //es 0.00
                rentabilidad.setMediaCancelacion(mediaClienteCancel);
                if(mediaGlobalCanceladas==0) { //si además la mediaGlobal también es 0 porque no hay ninguna reserva cancelada
                    rentabilidad.setDiferenciaMediaCancelacion(diferencia); //es 0.00
                }else {
                    //res.setDifMediaFactReservaCancel(new Float(-100.00)); //La diferencia sería el total, -100%
                    rentabilidad.setDiferenciaMediaCancelacion(new Float(-100.00));
                }               
                                
            }          
           // Liberamos recursos
           objRCancel.close();
           objRCancel = null;
           objRAceptadas.close();
           objRAceptadas = null;            
            //totales, atendidas y canceladas
            rentabilidad.setSolicitudes(sol);
            rentabilidad.setReservas(res);
            rentabilidad.setFacturacion(fac);
            rentabilidad.setFacturacionMedia(facMedia);
            lista.add(rentabilidad);
            
            //comprobamos si seguimos obteniendo filas
            if(top==Constantes.TOP_TODOS) {
                continuar = true;
            } else if ((top==Constantes.TOP_10)||(top==Constantes.TOP_20)) {               
                numFilas++;
                continuar = numFilas < top;
            }             
        }
        // Liberamos recursos
        objRS.close();
        objRS = null;        
        return lista;
    }

    private String obtenerListaPaises(FiltroLocalizacion filtroLoc) {
        //Países seleccionados
        List<PaisDTO> listaPaises = filtroLoc.getPaises();

        // Construimos la lista de idpais para obtener solo esas aulas
        String paises = "";
        PaisDTO pais = null;
        for (int i = 0; i < listaPaises.size(); i++) {
            pais = listaPaises.get(i);
            if (i == 0) {
                paises = pais.getIdPais().toString();
            } else {
                paises += ",'" + pais.getIdPais().toString() + "'";
            }
        }
        return paises;
    }

    private String obtenerListaProvincias(FiltroLocalizacion filtroLoc) {

        //Países seleccionados
        List<ProvinciaDTO> listaProvincias = filtroLoc.getProvincias();

        // Construimos la lista de idciudad para obtener solo esas aulas
        String provincias = "";
        ProvinciaDTO provincia = null;
        for (int i = 0; i < listaProvincias.size(); i++) {
            provincia = listaProvincias.get(i);
            if (i == 0) {
                provincias = provincia.getIdProvincia().toString();
            } else {
                provincias += ",'" + provincia.getIdProvincia().toString() + "'";
            }
        }
        return provincias;
    }

    private String obtenerListaCiudades(FiltroLocalizacion filtroLoc) {
        //Ciudades seleccionados
        List<CiudadDTO> listaCiudades = filtroLoc.getCiudades();

        // Construimos la lista de idciudad para obtener solo esas aulas
        String ciudades = "";
        CiudadDTO ciudad = null;
        for (int i = 0; i < listaCiudades.size(); i++) {
            ciudad = listaCiudades.get(i);
            if (i == 0) {
                ciudades = ciudad.getIdCiudad().toString();
            } else {
                ciudades += ",'" + ciudad.getIdCiudad().toString() + "'";
            }
        }
        
        return ciudades;
    }

    
}
